package cn.com.atlasdata.atops.schedule;

import cn.com.atlasdata.atops.helper.AtopsConstants;
import cn.com.atlasdata.atops.helper.Config;
import cn.com.atlasdata.atops.helper.ConnectHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 自动创建仪表板
 *
 * @author ZJH
 * @create 2018-09-26 15:56
 **/
@Component
@EnableConfigurationProperties({Config.class})
public class AutoCreateDashboard {
    @Autowired
    private Config config;
    private static final Logger log = LoggerFactory.getLogger(AutoCreateDashboard.class);

    /***
     * 检查endpoint列表，若有新增agent则导入新的dashborad
     */
    @Scheduled(cron = "0 0/3 * * * ?")//每3分钟检查一次
    private void CreateDashBoard(){
        System.out.println("仪表板检查启动...");
        List<String> endpoints = new ArrayList<String>();
        List<String> titles = new ArrayList<String>();
        endpoints = getEndpointList();
        titles = getDashBoardTitle();
        //遍历endpoints,检查是否有新的agent，若有则导入对于的dashboard
        //这里endpoint名称与dashboard的title是一致的
        for(String endpoint :endpoints){
            //若endpoint为 localhost.localdomain时跳过，因为 localhost.localdomain是open-falcon agent收集的服务器机器信息，因此不用自动生成dashboard
            if ("localhost.localdomain".equalsIgnoreCase(endpoint)){
                continue;
            }
            if (!titles.contains(endpoint)){//不存在则导入
                log.info("开始导入"+ endpoint + "仪表板...");
                importDashBoard(endpoint);
            }
        }

    }

    /***
     * 导入仪表板
     * @param title 导入仪表板的title
     */
    private void importDashBoard(String title){
        Connection conn = null;
        try
        {
            conn = ConnectHelper.getConnection(config.getDbcenter_host(),config.getDbcenter_port(),config.getDbcenter_dbname(),config.getDbcenter_username(),config.getDbcenter_password(), AtopsConstants.dbtype_postgres);
//            conn = ConnectHelper.getConnection("192.168.111.23","5432","grafana","dbuser","dbuser", AtopsConstants.dbtype_postgres);
            if(null != conn){
                String maxIdSql = "select id from dashboard order by id desc limit 1";//获取最大的id号
                int id = 1;//默认id为1
                String uid = System.currentTimeMillis()/1000 + "_" + title;
                String panels = "\"panels\":[{\"alert\":{\"conditions\":[{\"evaluator\":{\"params\":[70],\"type\":\"gt\"},\"operator\":{\"type\":\"and\"},\"query\":{\"params\":[\"A\",\"5m\",\"now\"]},\"reducer\":{\"params\":[],\"type\":\"max\"},\"type\":\"query\"}],\"executionErrorState\":\"alerting\",\"frequency\":\"60s\",\"handler\":1,\"name\":\"cpu使用率 alert\",\"noDataState\":\"no_data\",\"notifications\":[]},\"aliasColors\":{\"cpu利用率\":\"#f9d9f9\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"local\",\"description\":\"交易型数据库cpu使用率一般在40%以下\",\"fill\":1,\"gridPos\":{\"h\":5,\"w\":9,\"x\":0,\"y\":0},\"id\":12,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"cpu利用率\",\"refId\":\"A\",\"target\":\"pc4_orcl#host_CPU_utilization\",\"textEditor\":false}],\"thresholds\":[{\"colorMode\":\"critical\",\"fill\":true,\"line\":true,\"op\":\"gt\",\"value\":70}],\"timeFrom\":null,\"timeShift\":null,\"title\":\"cpu使用率\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"percent\",\"label\":null,\"logBase\":1,\"max\":\"100\",\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"alert\":{\"conditions\":[{\"evaluator\":{\"params\":[0.85],\"type\":\"lt\"},\"operator\":{\"type\":\"and\"},\"query\":{\"params\":[\"A\",\"5m\",\"now\"]},\"reducer\":{\"params\":[],\"type\":\"min\"},\"type\":\"query\"}],\"executionErrorState\":\"alerting\",\"frequency\":\"60s\",\"handler\":1,\"name\":\"缓存命中率buffer cache alert\",\"noDataState\":\"no_data\",\"notifications\":[]},\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":null,\"description\":\"DB缓存命中率，意义直观，一般而言都能达到90%以上，现在一般不作为数据库是否优化的参考指标。只有在命中率较低的情况下，才考虑是否缓存大小设置的不合理，或是业务模式导致数据共享率不高\",\"fill\":1,\"gridPos\":{\"h\":5,\"w\":7,\"x\":9,\"y\":0},\"id\":24,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"db cache缓存命中率\",\"refId\":\"A\",\"target\":\"pc4_orcl#db_cache_ratio_hit_ratio/name=DEFAULT\"}],\"thresholds\":[{\"colorMode\":\"critical\",\"fill\":true,\"line\":true,\"op\":\"lt\",\"value\":0.85}],\"timeFrom\":null,\"timeShift\":null,\"title\":\"缓存命中率buffer cache\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"decimals\":null,\"format\":\"percentunit\",\"label\":null,\"logBase\":1,\"max\":\"1\",\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"alert\":{\"conditions\":[{\"evaluator\":{\"params\":[0.85],\"type\":\"lt\"},\"operator\":{\"type\":\"and\"},\"query\":{\"params\":[\"B\",\"5m\",\"now\"]},\"reducer\":{\"params\":[],\"type\":\"min\"},\"type\":\"query\"}],\"executionErrorState\":\"alerting\",\"frequency\":\"60s\",\"handler\":1,\"name\":\"library cache库缓存命中率 alert\",\"noDataState\":\"no_data\",\"notifications\":[]},\"aliasColors\":{\"库缓存命中率\":\"#5195ce\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":null,\"description\":\"库（library）缓存命中率，代表的是shared pool中SQL共享的比例。命中率低表示SQL共享的少，需要较多SQL解析，这消耗CPU资源以及占用SGA内存，一般是由于业务代码SQL未使用绑定变量造成\",\"fill\":1,\"gridPos\":{\"h\":5,\"w\":8,\"x\":16,\"y\":0},\"id\":26,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"库缓存命中率\",\"refId\":\"B\",\"target\":\"pc4_orcl#library_cahce_ratio\"}],\"thresholds\":[{\"colorMode\":\"critical\",\"fill\":true,\"line\":true,\"op\":\"lt\",\"value\":0.85}],\"timeFrom\":null,\"timeShift\":null,\"title\":\"library cache库缓存命中率\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"percentunit\",\"label\":null,\"logBase\":1,\"max\":\"1\",\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"alert\":{\"conditions\":[{\"evaluator\":{\"params\":[20],\"type\":\"gt\"},\"operator\":{\"type\":\"and\"},\"query\":{\"params\":[\"A\",\"5m\",\"now\"]},\"reducer\":{\"params\":[],\"type\":\"max\"},\"type\":\"query\"}],\"executionErrorState\":\"alerting\",\"frequency\":\"60s\",\"handler\":1,\"name\":\"长事务会话数 alert\",\"noDataState\":\"no_data\",\"notifications\":[]},\"aliasColors\":{\"运行超过1秒的会话数量\":\"#bf1b00\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":null,\"description\":\"运行时间超过1秒的会话数量\",\"fill\":1,\"gridPos\":{\"h\":7,\"w\":8,\"x\":0,\"y\":5},\"id\":20,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"运行超过1秒的会话数量\",\"refId\":\"A\",\"target\":\"pc4_orcl#LongTransaction_count\"}],\"thresholds\":[{\"colorMode\":\"critical\",\"fill\":true,\"line\":true,\"op\":\"gt\",\"value\":20}],\"timeFrom\":null,\"timeShift\":null,\"title\":\"长事务会话数\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"decimals\":null,\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":\"0\",\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"alert\":{\"conditions\":[{\"evaluator\":{\"params\":[20],\"type\":\"lt\"},\"operator\":{\"type\":\"and\"},\"query\":{\"params\":[\"A\",\"5m\",\"now\"]},\"reducer\":{\"params\":[],\"type\":\"min\"},\"type\":\"query\"}],\"executionErrorState\":\"alerting\",\"frequency\":\"60s\",\"handler\":1,\"name\":\"共享池剩余量 alert\",\"noDataState\":\"no_data\",\"notifications\":[]},\"aliasColors\":{\"shared_pool剩余大小\":\"#f2c96d\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"local\",\"description\":\"共享池内存使用情况，如果没有剩余的共享池的话会引起大量硬解析，甚至数据库崩溃\",\"fill\":1,\"gridPos\":{\"h\":7,\"w\":8,\"x\":8,\"y\":5},\"id\":2,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"shared_pool剩余大小\",\"refId\":\"A\",\"target\":\"pc4_orcl#share_pool_free/type=bytes\",\"textEditor\":false}],\"thresholds\":[{\"colorMode\":\"critical\",\"fill\":true,\"line\":true,\"op\":\"lt\",\"value\":20}],\"timeFrom\":null,\"timeShift\":null,\"title\":\"共享池剩余量\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"decbytes\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":\"0\",\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"aliasColors\":{\"当前会话数\":\"#bf1b00\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"local\",\"description\":\"如果当前连接会话数达到允许的最大值后，新的会话无法连接到数据库\",\"fill\":1,\"gridPos\":{\"h\":7,\"w\":8,\"x\":16,\"y\":5},\"id\":16,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"允许最大会话数\",\"refId\":\"A\",\"target\":\"pc4_orcl#resource_limit_limit_value/INST_ID=1,resource_name=processes\",\"textEditor\":false},{\"alias\":\"当前会话数\",\"refId\":\"B\",\"target\":\"pc4_orcl#resource_limit_current_utilization/INST_ID=1,resource_name=processes\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"会话数限制\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"aliasColors\":{\"内存排序比率，比率越大越好，太小整要考虑调整，加大PGA\":\"#f9d9f9\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"local\",\"fill\":1,\"gridPos\":{\"h\":7,\"w\":12,\"x\":0,\"y\":12},\"id\":8,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"内存排序比率，比率越大越好，太小整要考虑调整，加大PGA\",\"refId\":\"A\",\"target\":\"pc4_orcl#memory_sort_ratio\",\"textEditor\":false}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"内存排序比\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"percent\",\"label\":null,\"logBase\":1,\"max\":\"100\",\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"aliasColors\":{\"CPU每秒负载时间\":\"#e5ac0e\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"local\",\"description\":\"CPU负载，表示主机CPU资源的总体使用量统计，一般要结合主机的CPU个数来看，即要看每个CPU的使用量情况。\",\"fill\":1,\"gridPos\":{\"h\":7,\"w\":12,\"x\":12,\"y\":12},\"id\":14,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"数据库占总CPU使用时间\",\"refId\":\"B\",\"target\":\"pc4_orcl#CPU_usage_per_sec\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"总CPU使用时间\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"none\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"aliasColors\":{\"每秒硬解析次数\":\"#e0752d\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"local\",\"description\":\"数据库硬解析,即SQL在每次执行前如果在library中没找到它的相关信息，则需要解析。一般要结合软解析及全部执行次数来看。硬解析多，则会消耗CPU资源\",\"fill\":1,\"gridPos\":{\"h\":8,\"w\":12,\"x\":0,\"y\":19},\"id\":6,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"每秒硬解析次数\",\"refId\":\"A\",\"target\":\"pc4_orcl#hard_parse_count_per_sec\",\"textEditor\":false}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"硬解析次数\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"local\",\"description\":\"解析时候只需要对其语法分析、权限对象分析之后就可以转到pga中查找，如果发现相同cursor直接取结果\",\"fill\":1,\"gridPos\":{\"h\":8,\"w\":12,\"x\":12,\"y\":19},\"id\":10,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"软解析百分比例\",\"refId\":\"A\",\"target\":\"pc4_orcl#soft_parse_ratio\",\"textEditor\":false}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"软解析比例\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"percent\",\"label\":null,\"logBase\":1,\"max\":\"100\",\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"aliasColors\":{\"每秒物理写bytes\":\"#5195ce\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"local\",\"description\":\"每秒物理写，即写入磁盘的数据，与数据库的修改量相关\",\"fill\":1,\"gridPos\":{\"h\":8,\"w\":12,\"x\":0,\"y\":27},\"id\":4,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"每秒物理写bytes\",\"refId\":\"A\",\"target\":\"pc4_orcl#physical_write_bytes_per_sec\",\"textEditor\":false}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"物理写bytes\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"decbytes\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":null,\"description\":\"每秒事务数,意义直观，此值的连续曲线图可以直观反映某个时段数据库运行的繁忙与否，与业务繁忙情况直接挂勾\",\"fill\":1,\"gridPos\":{\"h\":8,\"w\":12,\"x\":12,\"y\":27},\"id\":18,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"mixed\":true,\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"alias\":\"每秒事务数\",\"refId\":\"A\",\"target\":\"pc4_orcl#user_transaction_per_sec\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"每秒事务数\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}],\"yaxis\":{\"align\":false,\"alignLevel\":null}}," +
                        "{\"ajaxSorting\":false,\"columns\":[],\"datasource\":null,\"fontSize\":\"100%\",\"gridPos\":{\"h\":9,\"w\":24,\"x\":0,\"y\":35},\"id\":22,\"links\":[],\"mixed\":false,\"pageSize\":null,\"scroll\":true,\"showHeader\":true,\"sort\":{\"col\":0,\"desc\":true},\"styles\":[{\"alias\":\"Time\",\"dateFormat\":\"YYYY-MM-DD HH:mm:ss\",\"pattern\":\"Time\",\"type\":\"date\"},{\"alias\":\"\",\"colorMode\":null,\"colors\":[\"rgba(245, 54, 54, 0.9)\",\"rgba(237, 129, 40, 0.89)\",\"rgba(50, 172, 45, 0.97)\"],\"decimals\":2,\"pattern\":\"/.*/\",\"thresholds\":[],\"type\":\"number\",\"unit\":\"short\"}],\"targets\":[{\"refId\":\"A\",\"target\":\"TOP-SQL#pc4_orcl#select metric\"}],\"title\":\"TopSQL\",\"transform\":\"table\",\"type\":\"table\"}]," ;

                panels = panels.replace("pc4_orcl",title);
                try(Statement stmt = conn.createStatement() ) {
                    ResultSet rs = stmt.executeQuery(maxIdSql);
                    while (rs.next()){
                        id = rs.getInt("id") + 1;//新仪表板id为现有id的值再加1
                    }
                    //slug 的值与title一致
                    String sql = "INSERT INTO dashboard (id, version, slug, title, data, org_id, created, updated, updated_by, created_by, gnet_id, plugin_id, folder_id, is_folder, has_acl, uid) \n"
                            + "VALUES (" + id + ",1,'" + title + "','" + title + "',"
                            + "'{\"annotations\":{\"list\":[{\"builtIn\":1,\"datasource\":\"-- Grafana --\",\"enable\":true,\"hide\":true,\"iconColor\":\"rgba(0, 211, 255, 1)\",\"name\":\"标注和告警\",\"type\":\"dashboard\"}]},\n" +
                            "\"editable\":true,\n" +
                            "\"gnetId\":null,\n" +
                            "\"graphTooltip\":0,\n" +
                            "\"id\":1,\n" +
                            "\"links\":[],\n" +
                            panels +
                            "\"refresh\":\"1m\",\n" +
                            "\"schemaVersion\":16,\n" +
                            "\"style\":\"dark\",\n" +
                            "\"tags\":[],\n" +
                            "\"templating\":{\"list\":[]},\n" +
                            "\"time\":{\"from\":\"now-6h\",\"to\":\"now\"},\n" +
                            "\"timepicker\":{\"refresh_intervals\":[\"5s\",\"10s\",\"30s\",\"1m\",\"5m\",\"15m\",\"30m\",\"1h\",\"2h\",\"1d\"],\"time_options\":[\"5m\",\"15m\",\"1h\",\"6h\",\"12h\",\"24h\",\"2d\",\"7d\",\"30d\"]},\n" +
                            "\"timezone\":\"\",\n" +
                            "\"title\":\"" + title + "\",\n" +
                            "\"uid\":\""+ uid + "\",\n" +
                            "\"version\":1}',\n"+
                            "1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,1,0,'',0,false,false,\n" +
                            "'" + uid +"')" ;
                    if (!stmt.execute(sql)){//@return <code>true</code> if the first result is a <code>ResultSet</code>  object; <code>false</code> if it is an update count or there are no results
                        log.info("导入" + title + "仪表板成功！");
                    }else {
                        log.error("导入" + title + "仪表板失败！！！");
                    }
                }
            }
        }catch (Exception e){
            log.error("保存数据出错:" + e.toString(),e);
        }finally {
            try {
                if (null != conn && !conn.isClosed()) {
                    conn.close();
                    conn = null;
                }
            }catch (Exception e){
                log.error("关闭数据库连接出错:" + e.toString(),e);
            }
        }
    }
    /***
     *  获取endpoint 列表
     * @return endpoint list
     */
    private List<String> getEndpointList(){
        List<String> results = new ArrayList<String>();
        {
            Connection conn = null;
            try
            {
                conn = ConnectHelper.getConnection(config.getConfdb_host(),config.getConfdb_port(),config.getConfdb_dbname(),config.getConfdb_username(),config.getConfdb_password(), AtopsConstants.dbtype_mysql);
//                conn = ConnectHelper.getConnection("192.168.111.23","3306","graph","root","roooot", AtopsConstants.dbtype_mysql);
                if(null != conn){
                    String sql = "select endpoint from graph.endpoint";
                    try(Statement stmt = conn.createStatement() ) {
                        ResultSet rs = stmt.executeQuery(sql);
                        while (rs.next()) {
                            results.add(rs.getString("endpoint"));
                        }
                        rs.close();
                    }
                }
            }catch (SQLException e){
                log.error("获取endpoint列表出错:" + e.toString(),e);
            }finally {
                try {
                    if (null != conn && !conn.isClosed()) {
                        conn.close();
                        conn = null;
                    }
                }catch (Exception e){
                    log.error("关闭数据库连接出错:" + e.toString(),e);
                }
            }
        }
        return results;
    }

    /***
     * 获取grafana.dashboard title列表
     * @return title列表
     */
    private List<String> getDashBoardTitle(){
        List<String> results = new ArrayList<String>();
        {
            Connection conn = null;
            try
            {
//                conn = ConnectHelper.getConnection("192.168.111.23","5432","grafana","dbuser","dbuser", AtopsConstants.dbtype_postgres);
                conn = ConnectHelper.getConnection(config.getDbcenter_host(),config.getDbcenter_port(),config.getDbcenter_dbname(),config.getDbcenter_username(),config.getDbcenter_password(), AtopsConstants.dbtype_postgres);
                if(null != conn){
                    String sql = "select title from dashboard";
                    try(Statement stmt = conn.createStatement() ) {
                        ResultSet rs = stmt.executeQuery(sql);
                        while (rs.next()) {
                            results.add(rs.getString("title"));
                        }
                        rs.close();
                    }
                }
            }catch (SQLException e){
                log.error("获取grafana.dashboard title列表出错:" + e.toString(),e);
            }finally {
                try {
                    if (null != conn && !conn.isClosed()) {
                        conn.close();
                        conn = null;
                    }
                }catch (Exception e){
                    log.error("关闭数据库连接出错:" + e.toString(),e);
                }
            }
        }
        return results;
    }
}
